﻿// Author: Susana Messias nº 6046

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using LumenWorks.Framework.IO.Csv;
using Devart.Data.SQLite;
using Devart.Data;
using System.Windows.Forms;

namespace Interface
{
  public class Statistics
    {
      List<string> establishmentList; 
        List<string> degreeList;
        List<string> yearList;
        List<List<string>> list;

        // Creates the database
        private void creatDatabase()
        {
            var lista = new List<string>();
            lista.Add("rebcsv/reb0.csv");
            lista.Add("rebcsv/reb1.csv");
            lista.Add("rebcsv/reb2.csv");
            lista.Add("rebcsv/reb3.csv");
            lista.Add("rebcsv/reb4.csv");
            lista.Add("rebcsv/reb5.csv");
            lista.Add("rebcsv/reb6.csv");
            lista.Add("rebcsv/reb7.csv");
            lista.Add("rebcsv/reb8.csv");
            lista.Add("rebcsv/reb9.csv");                                         
            lista.Add("rebcsv/reb10.csv");

            SQLiteConnection database = new SQLiteConnection();
            database.ConnectionString = "Data Source=tabela.db;FailIfMissing=False;";
            database.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = database;

            cmd.CommandText = "BEGIN";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "drop table if exists tabela";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "create table if not exists tabela (Codigo_do_Docente varchar," +
                                                        "Nome varchar," +
                                                        "Ultimo_Grau varchar," +
                                                        "Curso varchar," +
                                                        "Categoria varchar," +
                                                        "Regime_de_tempo varchar," +
                                                        "Ano_Lectivo varchar," +
                                                        "Tipo_de_Estabelecimento varchar," +
                                                        "Nome_do_Estabelecimento varchar)";

            cmd.ExecuteNonQuery(); // allows cmd.CommandText.

            foreach (var file in lista)
            {
                Console.WriteLine(file);
                // open the file "data.csv" which is a CSV file with headers
                var csv =
                       new CsvReader(new StreamReader(file), true);

                var headers = csv.GetFieldHeaders();
                // Runs all the lines
                while (csv.ReadNextRecord())
                {

                    cmd.CommandText = "INSERT INTO tabela (Codigo_do_Docente, " +
                        "Nome, " +
                        "Ultimo_Grau, " +
                        "Curso, " +
                        "Categoria, " +
                        "Regime_de_tempo, " +
                        "Ano_Lectivo, " +
                        "Tipo_de_Estabelecimento, " +
                        "Nome_do_Estabelecimento) " +
                        "VALUES ('" +
                        csv[0].ToString().Replace("'", "ð") + "','" +
                        csv[1].ToString().Replace("'", "ð") + "','" +
                        csv[2].ToString().Replace("'", "ð") + "','" +
                        csv[3].ToString().Replace("'", "ð") + "','" +
                        csv[4].ToString().Replace("'", "ð") + "','" +
                        csv[5].ToString().Replace("'", "ð") + "','" +
                        csv[6].ToString().Replace("'", "ð") + "','" +
                        csv[7].ToString().Replace("'", "ð") + "','" +
                        csv[8].ToString().Replace("'", "ð") + "')";
                    cmd.ExecuteNonQuery();
                }
            }

            database.Commit();
            //cmd.CommandText = "END";
            //cmd.ExecuteNonQuery();
            database.Close();
        }


        public List<string> getEstablishmentList()
        {
            return establishmentList;
        }

        public List<string> getDegreeList()
        {
            return degreeList;
        }
        // Opens the Database
        public void getDatabase()
        {
            this.creatDatabase();
            list = new List<List<string>>();

            yearList = new List<string>();
            establishmentList = new List<string>();
            degreeList = new List<string>();


            SQLiteConnection database = new SQLiteConnection();
            database.ConnectionString = "Data Source=tabela.db;FailIfMissing=False;";
            database.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = database;

            cmd.CommandText = "SELECT * FROM tabela";


            SQLiteDataReader sqlite_datareader;

            sqlite_datareader = cmd.ExecuteReader();

            // The SQLiteDataReader allows us to run through the result lines:
            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
            {
                list.Add(new List<string>{sqlite_datareader[0].ToString().Replace("ð", "'"), 
                                           sqlite_datareader[1].ToString().Replace("ð", "'"), 
                                           sqlite_datareader[2].ToString().Replace("ð", "'"), 
                                           sqlite_datareader[3].ToString().Replace("ð", "'"), 
                                           sqlite_datareader[4].ToString().Replace("ð", "'"), 
                                           sqlite_datareader[5].ToString().Replace("ð", "'"), 
                                           sqlite_datareader[6].ToString().Replace("ð", "'"), 
                                           sqlite_datareader[7].ToString().Replace("ð", "'"), 
                                           sqlite_datareader[8].ToString().Replace("ð", "'")});
            }
            cmd.CommandText = "SELECT distinct Ano_Lectivo FROM tabela";
            sqlite_datareader = cmd.ExecuteReader();

            while (sqlite_datareader.Read())
            {
                yearList.Add(sqlite_datareader[0].ToString().Replace("ð", "'"));
            }
            yearList.Sort();

            cmd.CommandText = "SELECT distinct Nome_do_Estabelecimento FROM tabela";
            sqlite_datareader = cmd.ExecuteReader();

            while (sqlite_datareader.Read())
            {
                establishmentList.Add(sqlite_datareader[0].ToString());
            }
            establishmentList.Sort();

            cmd.CommandText = "SELECT distinct Ultimo_Grau FROM tabela";
            sqlite_datareader = cmd.ExecuteReader();

            while (sqlite_datareader.Read())
            {
                degreeList.Add(sqlite_datareader[0].ToString());
            }
            degreeList.Sort();


            database.Close();
        }


        // Creates the CSV of the number of teachers per establishment statistics
        public void establishment(string establishment)
        {

            var a = from year in this.yearList
                    select new List<string> {establishment, year,(from l in this.list
                                                                    where l[8] == establishment && l[6] == year
                                                                    select l).Count().ToString()};
                string filePath = establishment+" stats.csv";  
	            string delimiter = ",";  
	            
	            int length = a.Count();  
	            StringBuilder sb = new StringBuilder();  
	            foreach (var b in a)  
	                sb.AppendLine(string.Join(delimiter, b.ToArray()));  
	 
	            File.WriteAllText(filePath, sb.ToString());
        }

        // Creates the CSV of the number of teachers per establishment and per degree
        public void establishment(string establishment, string degree)
        {

            var a = from year in this.yearList
                    select new List<string> {establishment, year, degree,(from l in this.list
                                                                    where l[8] == establishment && l[6] == year && l[2] == degree
                                                                    select l).Count().ToString()};
            string filePath = establishment + " per " + degree + " stats.csv";
            string delimiter = ",";

            int length = a.Count();
            StringBuilder sb = new StringBuilder();
            foreach (var b in a)
                sb.AppendLine(string.Join(delimiter, b.ToArray()));

            File.WriteAllText(filePath, sb.ToString());
        }


    }  

    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}

